import asyncio
import os
import numpy as np
import pandas as pd
from datetime import date, timedelta
from typing import Optional, Tuple, Union, Dict, Any

import aiohttp
from bs4 import BeautifulSoup
from sqlalchemy.ext.asyncio import AsyncSession

from exceptions.exception import ServiceException
from module_kylin.dao.future_settlement_dao import FutureSettlementDao
from module_kylin.dao.future_account_daily_dao import FutureAccountDailyDao
from module_kylin.entity.vo.future_account_daily_vo import FutureAccountDailyModel
from module_kylin.entity.vo.future_account_vo import FutureAccountPageQueryModel
from module_kylin.entity.vo.future_settlement_vo import (
    DeleteFutureSettlementModel,
    FutureSettlementDownloadModel,
    FutureSettlementModel,
    FutureSettlementPageQueryModel,
    FutureSettlementQueryModel,
    ImportSettlementToDailyModel,
    BatchFutureSettlementDownloadModel,
)
from module_kylin.service.future_account_daily_service import FutureAccountDailyService
from utils.captcha_util import recognize_captcha
from utils.log_util import logger


class FutureSettlementService:
    """
    期货结算单服务层
    负责从中国期货市场监控中心下载结算单并管理结算单数据
    """

    # 中国期货市场监控中心URL配置
    BASE_URL = "https://investorservice.cfmmc.com"
    LOGIN_URL = BASE_URL + "/login.do"
    LOGOUT_URL = BASE_URL + "/logout.do"
    DATA_URL = BASE_URL + "/customer/setParameter.do"
    EXCEL_DAILY_DOWNLOAD_URL = (
        BASE_URL + "/customer/setupViewCustomerDetailFromCompanyWithExcel.do"
    )
    EXCEL_MONTHLY_DOWNLOAD_URL = (
        BASE_URL + "/customer/setupViewCustomerMonthDetailFromCompanyWithExcel.do"
    )

    # 请求头配置
    DEFAULT_HEADERS = {
        "Connection": "keep-alive",
        "User-Agent": "Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3626.121 Safari/537.36",
    }

    # 常量定义
    MAX_LOGIN_ATTEMPTS = 5
    SETTLEMENT_TYPE_DAILY = "D"  # 日报
    SETTLEMENT_TYPE_MONTHLY = "M"  # 月报
    SETTLEMENT_STATUS_PENDING = "0"  # 未下载状态
    SETTLEMENT_STATUS_DOWNLOADED = "1"  # 已下载状态
    SETTLEMENT_STATUS_IMPORTED = "2"  # 已导入状态

    @classmethod
    async def _recognize_captcha(
        cls, session: aiohttp.ClientSession
    ) -> Tuple[str, str]:
        """
        识别验证码并获取token

        :param session: aiohttp会话
        :return: (识别出的验证码, token)
        :raises Exception: 当验证码识别失败时
        """
        try:
            # 访问登录页面获取token和验证码URL
            response = await session.get(cls.LOGIN_URL, headers=cls.DEFAULT_HEADERS)
            html_content = await response.text()

            # 解析HTML获取token
            soup = BeautifulSoup(html_content, "lxml")
            if not (soup.body and soup.body.form and soup.body.form.input):
                raise Exception("无法从登录页面获取token")

            token = soup.body.form.input["value"]

            # 获取验证码图片URL
            img_tag = soup.body.form.img
            if not (img_tag and "src" in img_tag.attrs):
                raise Exception("无法获取验证码图片URL")

            verification_code_url = cls.BASE_URL + img_tag["src"]

            # 获取并识别验证码图片
            img_response = await session.get(verification_code_url)
            img_data = await img_response.read()

            # 编码图片并调用识别服务
            import base64

            base64_image = base64.b64encode(img_data).decode("utf-8")
            captcha_text = recognize_captcha(base64_image, min_length=6, max_length=6)

            if not captcha_text:
                raise Exception("验证码识别结果为空")

            logger.info(f"识别验证码成功: {captcha_text}")
            return captcha_text, token
        except Exception as e:
            logger.error(f"验证码识别失败: {str(e)}")
            raise Exception(f"验证码识别失败: {str(e)}")

    @classmethod
    def _get_token(cls, page_text: str) -> str:
        """
        从页面HTML中提取token

        :param page_text: 页面HTML文本
        :return: 提取的token
        :raises Exception: 当无法从页面提取token时
        """
        try:
            soup = BeautifulSoup(page_text, "lxml")
            if not soup.form or not soup.form.input:
                raise Exception("无法从页面HTML中提取token")
            return soup.form.input["value"]
        except AttributeError:
            raise Exception("无法从页面HTML中提取token")

    @classmethod
    def _format_date(cls, settlement_date: date, settlement_type: str) -> str:
        """
        根据结算单类型格式化日期

        :param settlement_date: 结算日期
        :param settlement_type: 结算单类型
        :return: 格式化后的日期字符串
        """
        if settlement_type == cls.SETTLEMENT_TYPE_MONTHLY:
            return settlement_date.strftime("%Y-%m")
        return settlement_date.strftime("%Y-%m-%d")

    @classmethod
    async def _set_parameters(
        cls,
        session: aiohttp.ClientSession,
        token: str,
        settlement_date: date,
        profit_calc_type: str = "day",
        settlement_type: str = "D",
    ) -> str:
        """
        设置结算单参数

        :param session: aiohttp会话
        :param token: 当前会话token
        :param settlement_date: 结算日期
        :param profit_calc_type: 盈亏计算方式：day(逐日盯市)或trade(逐笔对冲)
        :param settlement_type: 结算单类型：D(日报)、M(月报)
        :return: 更新后的token
        """
        # 格式化日期
        formatted_date = cls._format_date(settlement_date, settlement_type)

        # 准备请求参数
        post_data = {
            "org.apache.struts.taglib.html.TOKEN": token,
            "tradeDate": formatted_date,
            "byType": profit_calc_type,
        }

        # 发送请求设置参数
        response = await session.post(
            cls.DATA_URL, data=post_data, headers=cls.DEFAULT_HEADERS, timeout=5
        )

        # 获取响应文本并提取新token
        response_text = await response.text()
        return cls._get_token(response_text)

    @classmethod
    def _get_download_url(cls, settlement_type: str) -> str:
        """
        根据结算单类型获取下载URL

        :param settlement_type: 结算单类型
        :return: 对应的下载URL
        """
        return (
            cls.EXCEL_MONTHLY_DOWNLOAD_URL
            if settlement_type == cls.SETTLEMENT_TYPE_MONTHLY
            else cls.EXCEL_DAILY_DOWNLOAD_URL
        )

    @classmethod
    def _validate_settlement_type(cls, settlement_type: str) -> str:
        """
        验证并规范化结算单类型

        :param settlement_type: 结算单类型
        :return: 规范化后的结算单类型
        """
        # 转换为大写以支持大小写不敏感的比较
        if settlement_type:
            settlement_type = settlement_type.upper()
            if settlement_type == cls.SETTLEMENT_TYPE_MONTHLY:
                return cls.SETTLEMENT_TYPE_MONTHLY
        # 默认返回日报类型
        return cls.SETTLEMENT_TYPE_DAILY

    @classmethod
    async def _download_settlement_file(
        cls,
        session: aiohttp.ClientSession,
        token: str,
        settlement_date: date,
        username: str,
        account_code: str,
        profit_calc_type: str = "day",
        settlement_type: str = "D",
    ) -> Tuple[bytes, str, str]:
        """
        下载结算单文件

        :param session: aiohttp会话
        :param token: 当前会话token
        :param settlement_date: 结算日期
        :param username: 用户名
        :param account_code: 账户代码
        :param profit_calc_type: 盈亏计算方式：day(逐日盯市)或trade(逐笔对冲)
        :param settlement_type: 结算单类型：D(日报)、M(月报)
        :return: 文件内容、文件名和更新后的token
        """
        try:
            logger.info(
                f"开始设置下载参数: 日期={settlement_date}, 类型={profit_calc_type}, 结算单类型={settlement_type}"
            )

            # 设置参数并获取更新的token
            updated_token = await cls._set_parameters(
                session, token, settlement_date, profit_calc_type, settlement_type
            )
            # logger.info('下载参数设置成功')

            # 获取下载URL和格式化日期
            download_url = cls._get_download_url(settlement_type)
            formatted_date = (
                settlement_date.strftime("%Y-%m")
                if settlement_type == cls.SETTLEMENT_TYPE_MONTHLY
                else settlement_date.strftime("%Y%m%d")
            )

            # logger.info(f'准备下载结算单: URL={download_url}, 日期格式={formatted_date}')

            # 下载文件
            excel_response = await session.get(
                download_url, headers=cls.DEFAULT_HEADERS
            )

            # 检查响应状态
            if not excel_response.ok:
                error_msg = f"下载请求失败，状态码: {excel_response.status}, 状态: {excel_response.reason}"
                logger.error(error_msg)
                raise Exception(error_msg)

            # 读取文件内容
            content = await excel_response.read()

            # 验证下载内容
            if not content:
                raise Exception("下载的结算单内容为空")

            logger.info(f"结算单下载成功，文件大小: {len(content)} 字节")

            # 生成文件名
            normalized_type = cls._validate_settlement_type(settlement_type)
            date_str = settlement_date.strftime("%Y%m%d")
            file_name = (
                f"{account_code}_{date_str}_{normalized_type}_{profit_calc_type}.xls"
            )

            return content, file_name, updated_token
        except (aiohttp.ClientError, asyncio.TimeoutError) as e:
            error_msg = f"网络请求异常: {str(e)}"
            logger.error(error_msg)
            raise Exception(error_msg)
        except Exception as e:
            logger.error(f"下载结算单异常: {str(e)}")
            raise

    @classmethod
    async def get_settlement_list(
        cls,
        db: AsyncSession,
        query_model: Union[FutureSettlementPageQueryModel, FutureSettlementQueryModel],
        is_page: bool = False,
    ):
        """
        获取期货结算单列表

        :param db: 数据库会话
        :param query_model: 查询参数
        :param is_page: 是否分页
        :return: 结算单列表
        """
        return await FutureSettlementDao.get_settlement_list(db, query_model, is_page)

    @classmethod
    async def get_settlement_by_date_and_account(
        cls,
        db: AsyncSession,
        settlement_date: date,
        account_code: str,
        profit_calc_type: Optional[str] = None,
        settlement_type: Optional[str] = None,
    ) -> Optional[FutureSettlementModel]:
        """
        根据日期和账户获取结算单

        :param db: 数据库会话
        :param settlement_date: 结算日期
        :param account_code: 账户代码
        :param profit_calc_type: 盈亏计算方式（可选）
        :param settlement_type: 结算单类型（可选）
        :return: 结算单信息或None
        """
        # 如果提供了settlement_type，先规范化
        normalized_type = cls._validate_settlement_type(settlement_type) if settlement_type else None
        return await FutureSettlementDao.get_settlement_by_date_and_account(
            db,
            settlement_date,
            account_code,
            profit_calc_type=profit_calc_type,
            settlement_type=normalized_type,
        )

    @classmethod
    async def get_settlement_by_date_and_account_exclude_id(
        cls,
        db: AsyncSession,
        settlement_date: date,
        account_code: str,
        exclude_id: int,
        settlement_type: Optional[str] = None,
        profit_calc_type: Optional[str] = None,
    ):
        """
        根据日期和账户获取结算单（排除指定ID）

        :param db: 数据库会话
        :param settlement_date: 结算日期
        :param account_code: 账户代码
        :param exclude_id: 排除的结算单ID
        :param settlement_type: 结算单类型（可选）
        :param profit_calc_type: 盈亏计算方式（可选）
        :return: 结算单信息或None
        """
        # 如果提供了settlement_type，先规范化
        normalized_type = cls._validate_settlement_type(settlement_type) if settlement_type else None
        return await FutureSettlementDao.get_settlement_by_date_and_account_exclude_id(
            db,
            settlement_date,
            account_code,
            exclude_id,
            settlement_type=normalized_type,
            profit_calc_type=profit_calc_type,
        )

    @classmethod
    async def get_settlement_detail(
        cls, db: AsyncSession, settlement_id: int
    ) -> FutureSettlementModel:
        """
        获取期货结算单详情

        :param db: 数据库会话
        :param settlement_id: 结算单ID
        :return: 结算单详情
        :raises ServiceException: 当结算单不存在时
        """
        settlement = await FutureSettlementDao.get_settlement_by_id(db, settlement_id)
        if not settlement:
            raise ServiceException(f"结算单{settlement_id}不存在")
        return settlement

    @classmethod
    async def add_settlement(
        cls, db: AsyncSession, settlement: FutureSettlementModel
    ) -> str:
        """
        新增期货结算单

        :param db: 数据库会话
        :param settlement: 结算单信息
        :return: 操作结果
        :raises ServiceException: 当操作失败时
        """
        try:
            # 规范化结算单类型
            settlement.settlement_type = cls._validate_settlement_type(
                settlement.settlement_type
            )

            # 检查是否已存在相同日期、类型和盈亏计算方式的结算单
            existing_settlement = await cls.get_settlement_by_date_and_account(
                db,
                settlement.settlement_date,
                settlement.account_code,
                settlement.profit_calc_type,
                settlement.settlement_type,
            )

            if existing_settlement:
                # 如果已存在，直接更新
                await FutureSettlementDao.update_settlement(
                    db, existing_settlement.id, settlement
                )
                await db.commit()
                return "更新成功"
            else:
                # 如果不存在，添加新记录
                await FutureSettlementDao.add_settlement(db, settlement)
                await db.commit()
                return "添加成功"
        except Exception as e:
            await db.rollback()
            raise ServiceException(f"操作失败：{str(e)}")

    @classmethod
    async def update_settlement(
        cls, db: AsyncSession, settlement_id: int, settlement: FutureSettlementModel
    ) -> str:
        """
        更新期货结算单

        :param db: 数据库会话
        :param settlement_id: 结算单ID
        :param settlement: 结算单信息
        :return: 操作结果
        :raises ServiceException: 当操作失败时
        """
        try:
            # 检查结算单是否存在
            await cls.get_settlement_detail(db, settlement_id)

            # 检查是否存在重复记录（排除当前记录）
            duplicate_settlement = (
                await cls.get_settlement_by_date_and_account_exclude_id(
                    db,
                    settlement.settlement_date,
                    settlement.account_code,
                    settlement_id,
                    settlement_type=settlement.settlement_type,
                    profit_calc_type=settlement.profit_calc_type,
                )
            )

            if duplicate_settlement:
                raise ServiceException(
                    f"已存在日期为{settlement.settlement_date}和账户{settlement.account_code}的结算单"
                )

            # 规范化结算单类型
            settlement.settlement_type = cls._validate_settlement_type(
                settlement.settlement_type
            )

            # 执行更新操作
            await FutureSettlementDao.update_settlement(db, settlement_id, settlement)
            await db.commit()
            return "更新成功"
        except ServiceException:
            await db.rollback()
            raise
        except Exception as e:
            await db.rollback()
            raise ServiceException(f"更新失败：{str(e)}")

    @classmethod
    async def delete_settlement(
        cls, db: AsyncSession, delete_model: DeleteFutureSettlementModel
    ) -> str:
        """
        删除期货结算单

        :param db: 数据库会话
        :param delete_model: 删除参数
        :return: 操作结果
        :raises ServiceException: 当操作失败时
        """
        try:
            # 检查所有结算单是否存在
            for settlement_id in delete_model.settlement_ids:
                await cls.get_settlement_detail(db, settlement_id)  # 不存在会抛出异常

            # 批量删除
            await FutureSettlementDao.batch_delete_settlement(
                db, delete_model.settlement_ids
            )
            await db.commit()
            return "删除成功"
        except ServiceException:
            await db.rollback()
            raise
        except Exception as e:
            await db.rollback()
            raise ServiceException(f"删除失败：{str(e)}")

    @classmethod
    async def _login_to_cfmmc(
        cls,
        session: aiohttp.ClientSession,
        download_model: FutureSettlementDownloadModel,
    ) -> str:
        """
        登录中国期货市场监控中心

        :param session: aiohttp会话
        :param download_model: 下载参数
        :return: 登录后的token
        :raises Exception: 当登录失败时
        """
        login_attempt = 0

        while login_attempt < cls.MAX_LOGIN_ATTEMPTS:
            login_attempt += 1
            try:
                # 识别验证码并获取token
                captcha_code, token = await cls._recognize_captcha(session)

                # 准备登录数据
                login_data = {
                    "org.apache.struts.taglib.html.TOKEN": token,
                    "showSaveCookies": "",
                    "userID": download_model.username,
                    "password": download_model.password,
                    "vericode": captcha_code,
                }

                # 发送登录请求
                response = await session.post(
                    cls.LOGIN_URL,
                    data=login_data,
                    headers=cls.DEFAULT_HEADERS,
                    timeout=5,
                )

                # 获取登录响应内容
                response_text = await response.text()
                response_status = response.status
                response_headers = dict(response.headers)

                # 处理登录错误
                if "验证码错误" in response_text:
                    error_msg = "登录失败, 验证码错误, 请重试!"
                    logger.warning(
                        f"第{login_attempt}/{cls.MAX_LOGIN_ATTEMPTS}次尝试: {error_msg}"
                    )
                    if login_attempt >= cls.MAX_LOGIN_ATTEMPTS:
                        raise Exception(error_msg)
                    # 短暂延迟后重试
                    await asyncio.sleep(1)
                    continue

                if "请勿在公用电脑上记录您的查询密码" in response_text:
                    logger.error(
                        f"第{login_attempt}/{cls.MAX_LOGIN_ATTEMPTS}次尝试: 登录失败: 用户名密码错误"
                    )
                    raise Exception("用户名密码错误!")

                # 检查是否为其他类型的错误
                if (
                    not response.ok
                    or "错误" in response_text
                    or "失败" in response_text
                ):
                    error_msg = f"登录失败，响应状态: {response_status}，响应内容: {response_text[:200]}..."
                    logger.error(
                        f"第{login_attempt}/{cls.MAX_LOGIN_ATTEMPTS}次尝试: {error_msg}"
                    )
                    if login_attempt >= cls.MAX_LOGIN_ATTEMPTS:
                        raise Exception(error_msg)
                    # 短暂延迟后重试
                    await asyncio.sleep(1)
                    continue

                # 记录详细的响应信息用于调试
                # logger.info(f'登录响应状态: {response_status}, 响应内容长度: {len(response_text)}, 响应头: {response_headers}')

                # 登录成功，更新token
                token = cls._get_token(response_text)
                # logger.info(f'第{login_attempt}/{cls.MAX_LOGIN_ATTEMPTS}次尝试: 登录成功')
                return token

            except Exception as e:
                if "用户名密码错误" in str(e):
                    raise

                # 记录详细的错误信息，包括异常类型和错误消息
                logger.error(
                    f"第{login_attempt}/{cls.MAX_LOGIN_ATTEMPTS}次尝试: 登录失败: 异常类型: {type(e).__name__}, 错误消息: {str(e)}"
                )
                if login_attempt >= cls.MAX_LOGIN_ATTEMPTS:
                    raise
                # 短暂延迟后重试
                await asyncio.sleep(1)

        raise Exception("登录失败：达到最大尝试次数")

    @classmethod
    async def _save_file(cls, content: bytes, file_name: str) -> str:
        """
        保存文件到本地磁盘

        :param content: 文件内容
        :param file_name: 文件名
        :return: 文件保存路径
        """
        settlement_dir = os.path.join("assets", "settlements")
        os.makedirs(settlement_dir, exist_ok=True)
        file_path = os.path.join(settlement_dir, file_name)

        with open(file_path, "wb") as f:
            f.write(content)

        logger.info(f"结算单已保存到: {file_path}")
        return file_path

    @classmethod
    async def _update_settlement_status(
        cls,
        db: AsyncSession,
        download_model: FutureSettlementDownloadModel,
        file_name: str,
    ):
        """
        更新结算单状态为已下载，如果不存在则插入新记录

        :param db: 数据库会话
        :param download_model: 下载参数
        :param file_name: 文件名
        """
        try:
            # 验证结算单类型
            settlement_type = cls._validate_settlement_type(
                download_model.settlement_type
            )

            # 查找对应结算单，通过settlement_date、account_code、profit_calc_type和settlement_type精确查找
            existing_settlement = await cls.get_settlement_by_date_and_account(
                db,
                download_model.settlement_date,
                download_model.account_code,
                download_model.profit_calc_type,
                settlement_type,
            )

            if existing_settlement:
                # 准备更新数据
                update_data = FutureSettlementModel()
                update_data.settlement_status = cls.SETTLEMENT_STATUS_DOWNLOADED
                update_data.file_name = file_name
                update_data.settlement_type = cls._validate_settlement_type(
                    existing_settlement.settlement_type
                )

                # 更新结算单信息
                await FutureSettlementDao.update_settlement(
                    db, existing_settlement.id, update_data
                )
                # 使用flush代替commit，避免在循环中频繁提交事务
                await db.flush()
                # logger.info(f'结算单ID {existing_settlement.id} 状态已更新为已下载')
            else:
                # 如果不存在记录，则插入新的结算单记录
                # 准备新记录数据
                new_settlement = FutureSettlementModel()
                new_settlement.account_code = download_model.account_code
                new_settlement.settlement_date = download_model.settlement_date
                new_settlement.settlement_type = settlement_type
                new_settlement.profit_calc_type = download_model.profit_calc_type
                new_settlement.file_name = file_name
                new_settlement.settlement_status = cls.SETTLEMENT_STATUS_DOWNLOADED

                # 插入新记录
                await FutureSettlementDao.add_settlement(db, new_settlement)
                # 使用flush代替commit，避免在循环中频繁提交事务
                await db.flush()
                # logger.info(f'已插入新的结算单记录: 账户{download_model.account_code}, 日期{download_model.settlement_date}, 文件名{file_name}')
        except Exception as e:
            # 如果发生错误，回滚当前事务
            await db.rollback()
            logger.error(f"更新结算单状态失败: {str(e)}")
            raise

    @classmethod
    async def download_settlement(
        cls, db: AsyncSession, download_model: FutureSettlementDownloadModel
    ) -> str:
        """
        下载指定日期的期货结算单

        :param db: 数据库会话
        :param download_model: 下载参数，包含用户名、密码和结算日期等信息
        :return: 下载结果信息
        :raises ServiceException: 当下载失败时
        """
        try:
            # 规范化结算单类型
            settlement_type = cls._validate_settlement_type(
                download_model.settlement_type
            )

            # 检查是否已存在该结算单，通过settlement_date、account_code、profit_calc_type和settlement_type精确查找
            existing_settlement = await cls.get_settlement_by_date_and_account(
                db,
                download_model.settlement_date,
                download_model.account_code,
                download_model.profit_calc_type,
                settlement_type,
            )

            # 如果存在相同日期、账户、盈亏计算方式和结算单类型的结算单，且状态为已下载，则不再下载
            if (
                existing_settlement
                and existing_settlement.settlement_status
                == cls.SETTLEMENT_STATUS_DOWNLOADED
            ):
                raise ServiceException(
                    f"结算单已存在：日期{download_model.settlement_date}，账户{download_model.account_code}，"
                    f"盈亏计算方式{download_model.profit_calc_type}，结算单类型{settlement_type}"
                )

            async with aiohttp.ClientSession() as session:
                # 登录中国期货市场监控中心
                token = await cls._login_to_cfmmc(session, download_model)

                # 下载结算单文件
                file_content, file_name, _ = await cls._download_settlement_file(
                    session,
                    token,
                    download_model.settlement_date,
                    download_model.username,
                    download_model.account_code,
                    download_model.profit_calc_type,
                    download_model.settlement_type,
                )

                # 保存文件到本地
                await cls._save_file(file_content, file_name)

                # 更新数据库中结算单状态
                await cls._update_settlement_status(db, download_model, file_name)

                # logger.info(f'下载结算单 {file_name} 完成!')
                # 返回更详细的信息，明确表示已在后台更新状态
                return {
                    "message": f"结算单{download_model.settlement_date}下载成功",
                    "fileName": file_name,
                    "status": "DOWNLOADED",
                    "updatedInBackend": True,
                }
        except Exception as e:
            error_msg = str(e)
            logger.error(f"下载结算单异常: {error_msg}")
            # 确保错误信息不为空
            if not error_msg.strip():
                error_msg = "下载结算单过程中发生未知错误"
            raise ServiceException(f"下载结算单失败: {error_msg}")

    @classmethod
    async def batch_download_settlement(
        cls, db: AsyncSession, download_model: BatchFutureSettlementDownloadModel
    ) -> Dict[str, Any]:
        """
        批量下载期货结算单

        :param db: 数据库会话
        :param download_model: 批量下载参数
        :return: 下载结果统计信息
        """
        try:
            # 导入必要的模块
            from typing import Dict, Any
            from module_kylin.service.future_trade_cal_service import (
                FutureTradeCalService,
            )
            from module_kylin.dao.future_account_dao import FutureAccountDao
            from module_kylin.entity.do.future_account_do import KylinFutureAccount
            import aiohttp

            # 初始化统计信息
            stats = {
                "total_tasks": 0,
                "success_count": 0,
                "skip_count": 0,
                "fail_count": 0,
                "fail_messages": [],
            }

            logger.info(f"开始批量下载结算单，参数: {download_model.model_dump()}")

            # 生成日期列表
            dates = []
            current_date = download_model.begin_date
            while current_date <= download_model.end_date:
                dates.append(current_date)
                current_date += timedelta(days=1)

            # 获取所有账户信息，构建账户映射
            account_map = {}
            all_accounts = await FutureAccountDao.get_account_list(
                db, FutureAccountPageQueryModel()
            )
            
            # 根据account_codes决定处理哪些账户
            if download_model.account_codes:
                # 如果指定了账户代码，则只处理这些账户
                for account in all_accounts:
                    if account.account_code in download_model.account_codes:
                        account_map[account.account_code] = account
            else:
                # 如果没有指定账户代码（空列表），则处理所有账户
                for account in all_accounts:
                    account_map[account.account_code] = account

            # 按账户分组准备下载任务
            account_tasks = {}
            for account_code in account_map.keys():
                account_tasks[account_code] = []

                for settlement_date in dates:
                    for settlement_type in download_model.settlement_types:
                        # 对于月报，检查是否为月末日期
                        if settlement_type == "M":
                            # 计算当月最后一天
                            if settlement_date.month == 12:
                                last_day = date(
                                    settlement_date.year + 1, 1, 1
                                ) - timedelta(days=1)
                            else:
                                last_day = date(
                                    settlement_date.year, settlement_date.month + 1, 1
                                ) - timedelta(days=1)
                            if settlement_date != last_day:
                                continue

                        for profit_calc_type in download_model.profit_calc_types:
                            task = {
                                "account_code": account_code,
                                "settlement_date": settlement_date,
                                "settlement_type": settlement_type,
                                "profit_calc_type": profit_calc_type,
                            }
                            account_tasks[account_code].append(task)
                            stats["total_tasks"] += 1

            logger.info(f'共生成 {stats["total_tasks"]} 个下载任务')

            # 按账户执行下载任务，每个账户只登录一次
            for account_code, tasks in account_tasks.items():
                if not tasks:
                    continue

                # 获取账户信息
                account = account_map.get(account_code)
                if (
                    not account
                    or not account.settlement_account
                    or not account.settlement_password
                ):
                    # 为该账户的所有任务标记失败
                    for task in tasks:
                        stats["fail_count"] += 1
                        error_msg = f'{task["account_code"]} {task["settlement_date"]}：账户的登录信息不完整'
                        stats["fail_messages"].append(error_msg)
                        logger.error(f"下载失败: {error_msg}")
                    continue

                # 创建下载模型（用于登录）
                login_model = FutureSettlementDownloadModel(
                    username=account.settlement_account,
                    password=account.settlement_password,
                    account_code=account_code,
                    settlement_date=tasks[0]["settlement_date"],  # 任意日期用于登录
                    settlement_type=tasks[0]["settlement_type"],
                    profit_calc_type=tasks[0]["profit_calc_type"],
                )

                # 为该账户创建一个会话，登录一次
                async with aiohttp.ClientSession() as session:
                    try:
                        # 登录中国期货市场监控中心
                        token = await cls._login_to_cfmmc(session, login_model)
                        logger.info(f"账户 {account_code} 登录成功")

                        # 使用同一个session和token下载该账户的所有结算单
                        for task_index, task in enumerate(tasks):
                            try:
                                logger.info(
                                    f"处理账户 {account_code} 的任务 {task_index+1}/{len(tasks)}: {task}"
                                )

                                # 检查是否为交易日
                                trade_cal = (
                                    await FutureTradeCalService.get_trade_cal_by_date(
                                        db, task["settlement_date"].strftime("%Y%m%d")
                                    )
                                )
                                if not trade_cal or not trade_cal.is_open:
                                    stats["skip_count"] += 1
                                    logger.info(
                                        f'跳过非交易日: {task["settlement_date"]}'
                                    )
                                    continue

                                # 检查是否已下载
                                existing_settlement = (
                                    await cls.get_settlement_by_date_and_account(
                                        db,
                                        task["settlement_date"],
                                        task["account_code"],
                                        profit_calc_type=task["profit_calc_type"],
                                        settlement_type=task["settlement_type"],
                                    )
                                )
                                if (
                                    existing_settlement
                                    and existing_settlement.settlement_status
                                    in [
                                        cls.SETTLEMENT_STATUS_DOWNLOADED,
                                        cls.SETTLEMENT_STATUS_IMPORTED,
                                    ]
                                ):
                                    stats["skip_count"] += 1
                                    logger.info(
                                        f'跳过已下载: {task["account_code"]} {task["settlement_date"]}'
                                    )
                                    continue

                                # 创建下载模型
                                single_download_model = FutureSettlementDownloadModel(
                                    username=account.settlement_account,
                                    password=account.settlement_password,
                                    account_code=task["account_code"],
                                    settlement_date=task["settlement_date"],
                                    settlement_type=task["settlement_type"],
                                    profit_calc_type=task["profit_calc_type"],
                                )

                                # 直接调用内部方法下载，避免重复登录
                                # 下载结算单文件
                                file_content, file_name, _ = (
                                    await cls._download_settlement_file(
                                        session,
                                        token,
                                        single_download_model.settlement_date,
                                        single_download_model.username,
                                        single_download_model.account_code,
                                        single_download_model.profit_calc_type,
                                        single_download_model.settlement_type,
                                    )
                                )

                                # 保存文件到本地
                                await cls._save_file(file_content, file_name)

                                # 更新数据库中结算单状态
                                await cls._update_settlement_status(
                                    db, single_download_model, file_name
                                )

                                stats["success_count"] += 1
                                logger.info(
                                    f"下载成功: 结算单{single_download_model.settlement_date}，文件名：{file_name}"
                                )

                                # 除了最后一个任务外，每个任务下载成功后休息5秒
                                if task_index < len(tasks) - 1:
                                    logger.info("等待5秒后继续下载下一个结算单...")
                                    await asyncio.sleep(5)

                            except Exception as e:
                                stats["fail_count"] += 1
                                error_msg = f'{task["account_code"]} {task["settlement_date"]}：{str(e)}'
                                stats["fail_messages"].append(error_msg)
                                logger.error(f"下载失败: {error_msg}")
                    except Exception as e:
                        # 登录失败，为该账户的所有任务标记失败
                        login_error_msg = str(e)
                        logger.error(f"账户 {account_code} 登录失败: {login_error_msg}")
                        for task in tasks:
                            stats["fail_count"] += 1
                            error_msg = f'{task["account_code"]} {task["settlement_date"]}：登录失败 - {login_error_msg}'
                            stats["fail_messages"].append(error_msg)

            # 进一步优化返回结果，不仅限制失败消息数量，还简化整个返回结构
            optimized_stats = {
                "total_tasks": stats["total_tasks"],
                "success_count": stats["success_count"],
                "skip_count": stats["skip_count"],
                "fail_count": stats["fail_count"],
            }

            # 只在有失败时才添加部分失败消息，并且每条消息限制长度
            if stats["fail_count"] > 0:
                short_fail_messages = []
                for msg in stats["fail_messages"][:3]:  # 只保留前3条
                    # 每条消息限制在100字符以内
                    short_msg = msg[:100] + ("..." if len(msg) > 100 else "")
                    short_fail_messages.append(short_msg)

                # 如果还有更多失败消息，添加一个简单的汇总信息
                if len(stats["fail_messages"]) > 3:
                    short_fail_messages.append(
                        f'还有{len(stats["fail_messages"]) - 3}条失败信息'
                    )

                optimized_stats["fail_messages"] = short_fail_messages

            logger.info(f"批量下载完成，结果: {stats}")
            # 提交事务，确保所有更改都保存到数据库
            await db.commit()
            return optimized_stats

        except Exception as e:
            # 发生异常时回滚事务
            await db.rollback()
            logger.error(f"批量下载结算单异常: {str(e)}")
            raise ServiceException(f"批量下载结算单失败: {str(e)}")

    @classmethod
    async def import_settlement_to_daily(
        cls, db: AsyncSession, import_model: ImportSettlementToDailyModel
    ) -> str:
        """
        导入期货结算单到资金日报

        :param db: 数据库会话
        :param import_model: 导入参数，包含结算单信息
        :return: 导入结果信息
        :raises ServiceException: 当导入失败时
        """
        try:
            # 首先验证关键字段
            logger.info(
                f"导入参数类型: {type(import_model)}, 属性: {dir(import_model)}"
            )
            logger.info(
                f"导入参数account_code值: {import_model.account_code}, 类型: {type(import_model.account_code)}"
            )
            logger.info(
                f"导入参数settlement_date值: {import_model.settlement_date}, 类型: {type(import_model.settlement_date)}"
            )

            if not import_model.account_code:
                logger.error(
                    "导入参数中账户代码为空，值: %s", import_model.account_code
                )
                raise ServiceException("账户代码不能为空")
            if not import_model.settlement_date:
                logger.error(
                    "导入参数中结算日期为空，值: %s", import_model.settlement_date
                )
                raise ServiceException("结算日期不能为空")

            logger.info(
                f"开始导入结算单: {import_model.file_name}, 账户代码: {import_model.account_code}, 结算日期: {import_model.settlement_date}"
            )

            # 构建文件路径
            file_path = os.path.join("assets", "settlements", import_model.file_name)

            # 检查文件是否存在
            if not os.path.exists(file_path):
                raise ServiceException(f"结算单文件不存在: {file_path}")

            # 读取Excel文件
            logger.info(f"开始读取Excel文件: {file_path}")
            excel_data = pd.ExcelFile(file_path)

            # 记录文件的sheet信息
            logger.info(f"Excel文件包含的所有sheet: {excel_data.sheet_names}")
            logger.info(f"Excel文件sheet数量: {len(excel_data.sheet_names)}")

            # 根据结算单类型确定sheet名称
            if import_model.settlement_type == cls.SETTLEMENT_TYPE_DAILY:
                sheet_name = "客户交易结算日报"
                logger.info(f"结算单类型为日报，尝试使用sheet: {sheet_name}")
            else:  # 月报
                sheet_name = "客户交易结算月报"
                logger.info(f"结算单类型为月报，尝试使用sheet: {sheet_name}")

            # 检查sheet是否存在
            if sheet_name not in excel_data.sheet_names:
                logger.error(
                    f"sheet {sheet_name} 不存在于文件中，可用的sheet有: {excel_data.sheet_names}"
                )
                raise ServiceException(f"文件中找不到sheet: {sheet_name}")

            # 读取sheet数据
            logger.info(f"开始读取sheet: {sheet_name}")
            df = pd.read_excel(file_path, sheet_name=sheet_name)

            # 解析资金信息
            fund_data = cls._parse_settlement_fund_data(
                df, import_model.profit_calc_type
            )

            # 检查是否存在记录
            from module_kylin.dao.future_account_daily_dao import FutureAccountDailyDao

            # 先查询是否存在记录
            existing_record = (
                await FutureAccountDailyDao.get_account_daily_by_date_and_account(
                    db, import_model.settlement_date, import_model.account_code
                )
            )

            # 创建资金日报对象
            # 再次验证关键字段
            if not import_model.account_code:
                logger.error("创建资金日报对象时账户代码为空")
                raise ServiceException("账户代码不能为空")
            if not import_model.settlement_date:
                logger.error("创建资金日报对象时结算日期为空")
                raise ServiceException("结算日期不能为空")

            # 直接创建FutureAccountDailyModel对象
            account_daily = FutureAccountDailyModel()
            account_daily.account_code = import_model.account_code
            account_daily.settlement_date = import_model.settlement_date

            # 根据结算单类型确定需要处理的字段
            common_fields = [
                "daily_deposit_withdrawal",
                "daily_fee",
                "equity",
                "margin_used",
                "available_funds",
                "risk_ratio",
            ]
            day_fields = ["daily_profit_day", "daily_balance_day"]
            trade_fields = [
                "close_profit_trade",
                "daily_balance_trade",
                "float_profit_trade",
            ]

            # 遍历处理需要的字段
            fields_to_process = common_fields.copy()
            if import_model.profit_calc_type == "day":  # 逐日盯市
                fields_to_process.extend(day_fields)
                fields_to_process.append("previous_balance_day")
            else:  # 逐笔对冲
                fields_to_process.extend(trade_fields)
                fields_to_process.append("previous_balance_trade")

            for field_name in fields_to_process:
                if field_name in fund_data:
                    value = fund_data[field_name]
                    # 只处理非None和非NaN的值
                    import numpy as np

                    if value is not None and (
                        not isinstance(value, float) or not np.isnan(value)
                    ):
                        setattr(account_daily, field_name, value)

            # 再次进行显式非空检查
            if not account_daily.account_code or not account_daily.settlement_date:
                raise ServiceException("账户代码或结算日期为空，导入失败")

            # 创建安全对象以确保数据正确传递
            safe_account_daily = FutureAccountDailyModel()
            safe_account_daily.account_code = import_model.account_code
            safe_account_daily.settlement_date = import_model.settlement_date

            # 根据结算单类型确定字段列表
            if import_model.profit_calc_type == "day":  # 逐日盯市
                fields_to_copy = common_fields + day_fields + ["previous_balance_day"]
                fields_to_keep_none = trade_fields + ["previous_balance_trade"]
            else:  # 逐笔对冲
                fields_to_copy = (
                    common_fields + trade_fields + ["previous_balance_trade"]
                )
                fields_to_keep_none = day_fields + ["previous_balance_day"]

            # 显式设置对立类型字段为None，确保这些字段不会被更新
            for field in fields_to_keep_none:
                setattr(safe_account_daily, field, None)

            # 只复制相关字段
            for field in fields_to_copy:
                value = getattr(account_daily, field)
                import numpy as np

                if value is not None and (
                    not isinstance(value, float) or not np.isnan(value)
                ):
                    setattr(safe_account_daily, field, value)

            # 添加或更新数据库记录
            try:
                # 根据记录是否存在选择插入或更新操作
                if existing_record:
                    # 更新已存在的记录
                    # 设置ID以便更新
                    if hasattr(existing_record, "id"):
                        safe_account_daily.id = existing_record.id
                    elif isinstance(existing_record, dict) and "id" in existing_record:
                        safe_account_daily.id = existing_record["id"]
                    else:
                        logger.error("无法获取现有记录的ID")
                        raise ServiceException("无法更新记录: 无法获取记录ID")

                    await FutureAccountDailyDao.update_account_daily(
                        db, safe_account_daily.id, safe_account_daily
                    )
                else:
                    # 插入新记录
                    await FutureAccountDailyDao.add_account_daily(
                        db, safe_account_daily
                    )

                # 提交事务
                await db.commit()
            except Exception as e:
                logger.error(f"数据库操作失败: {str(e)}")
                await db.rollback()
                raise

            logger.info(
                f"结算单导入成功: {import_model.account_code} {import_model.settlement_date}"
            )

            # 更新结算单状态为已导入
            try:
                # 先查询结算单ID（使用更简单的查询方式）
                from sqlalchemy import select
                from module_kylin.entity.do.future_settlement_do import (
                    KylinFutureSettlement,
                )

                # 构建查询
                logger.info(f"开始查询结算单 - 日期: {import_model.settlement_date}, 账户: {import_model.account_code}, 类型: {import_model.settlement_type}, 盈亏计算方式: {import_model.profit_calc_type}")
                
                # 先尝试精确匹配查询
                query = select(KylinFutureSettlement.id).where(
                    KylinFutureSettlement.settlement_date == import_model.settlement_date,
                    KylinFutureSettlement.account_code == import_model.account_code,
                    KylinFutureSettlement.settlement_type == import_model.settlement_type,
                    KylinFutureSettlement.profit_calc_type == import_model.profit_calc_type,
                )
                result = await db.execute(query)
                settlement_id = result.scalar()
                
                # 如果精确匹配找不到且是逐笔对冲类型，尝试查找对应日期和账户的任何盈利计算方式
                if not settlement_id and import_model.profit_calc_type == 'trade':
                    logger.info(f"精确匹配未找到结算单，尝试查找相同日期和账户的任何盈利计算方式")
                    query = select(KylinFutureSettlement.id).where(
                        KylinFutureSettlement.settlement_date == import_model.settlement_date,
                        KylinFutureSettlement.account_code == import_model.account_code,
                        KylinFutureSettlement.settlement_type == import_model.settlement_type
                    )
                    result = await db.execute(query)
                    settlement_id = result.scalar()
                    
                    if settlement_id:
                        logger.info(f"找到匹配的结算单ID: {settlement_id}，但盈利计算方式不匹配")

                if settlement_id:
                    # 准备更新数据
                    update_data = FutureSettlementModel()
                    update_data.settlement_status = cls.SETTLEMENT_STATUS_IMPORTED
                    update_data.file_name = import_model.file_name
                    update_data.settlement_type = cls._validate_settlement_type(import_model.settlement_type)
                    # 确保更新profit_calc_type字段以匹配当前导入的类型
                    update_data.profit_calc_type = import_model.profit_calc_type

                    # 直接更新结算单状态
                    await FutureSettlementDao.update_settlement(
                        db, settlement_id, update_data
                    )
                    logger.info(f"结算单ID {settlement_id} 状态已更新为已导入，盈利计算方式: {import_model.profit_calc_type}")
                else:
                    logger.warning(f"未找到匹配的结算单记录 - 日期: {import_model.settlement_date}, 账户: {import_model.account_code}, 类型: {import_model.settlement_type}, 盈亏计算方式: {import_model.profit_calc_type}")
            except Exception as e:
                logger.error(f"更新结算单状态时发生错误: {str(e)}")
                # 不抛出异常，允许导入继续完成
                pass

            return f"结算单导入成功: {import_model.file_name}"

        except ServiceException:
            logger.error("捕获到ServiceException异常，原样抛出")
            # 确保事务回滚
            try:
                await db.rollback()
                logger.info("捕获到ServiceException，事务已回滚")
            except:
                pass
            raise
        except Exception as e:
            logger.error(f"导入结算单到资金日报失败: {str(e)}")
            logger.error("详细错误信息:", exc_info=True)
            # 确保事务回滚
            try:
                await db.rollback()
                logger.info("捕获到普通异常，事务已回滚")
            except:
                pass
            raise ServiceException(f"导入失败: {str(e)}")

    @classmethod
    def _parse_settlement_fund_data(
        cls, df: pd.DataFrame, profit_calc_type: str = "day"
    ) -> dict:
        """
        从结算单DataFrame中解析资金信息（针对客户交易结算日报格式）

        :param df: 结算单的DataFrame
        :param profit_calc_type: 盈亏计算方式，'day'表示逐日盯市，'trade'表示逐笔对冲
        :return: 解析后的资金数据字典
        :raises Exception: 当解析失败时
        """
        import numpy as np

        fund_data = {}

        # 记录DataFrame的详细信息用于调试
        logger.info("=" * 80)
        logger.info("开始解析资金数据 - 详细调试日志")
        logger.info(f"DataFrame形状: ({df.shape[0]}, {df.shape[1]})")
        logger.info(f"行数: {len(df)}, 列数: {len(df.columns)}")
        logger.info(f"数据是否包含NaN: {df.isna().any().any()}")
        logger.info(f"NaN统计:\n{df.isna().sum()}")
        logger.info(f"非空单元格统计:\n{df.notna().sum()}")
        logger.info(f"列名信息: {list(df.columns)}")
        logger.info(f"前3行数据:\n{df.head(3).to_string()}")
        logger.info(f"数据类型:\n{df.dtypes}")
        logger.info(f"盈亏计算方式: {profit_calc_type}")

        # 统一解析方法：结合关键字匹配和表格结构解析的优点
        try:
            logger.info("=" * 80)
            logger.info("开始统一解析方法 - 结合关键字匹配和表格结构解析")

            # 记录DataFrame基本信息
            total_rows = len(df)
            total_cols = df.shape[1]
            logger.info(f"数据框维度 - 行数: {total_rows}, 列数: {total_cols}")
            logger.info(f"开始扫描前{min(total_rows, 100)}行表格结构")

            # 将DataFrame转换为字符串列表，方便搜索
            df_str = df.astype(str).values.tolist()
            logger.info(
                f"转换为字符串列表后的数据形状: ({len(df_str)}, {len(df_str[0]) if df_str else 0})"
            )

            # 定义基础关键字映射
            base_keyword_mapping = {
                "当日存取合计": "daily_deposit_withdrawal",
                "平仓盈亏": "close_profit_trade",
                "当日手续费": "daily_fee",
                "浮动盈亏": "float_profit_trade",
                "客户权益": "equity",
                "保证金占用": "margin_used",
                "可用资金": "available_funds",
                "风险度": "risk_ratio",
            }

            # 根据盈亏计算方式动态设置关键字映射
            if profit_calc_type == "day":  # 逐日盯市
                logger.info(
                    '使用逐日盯市模式，"当日盈亏"映射到daily_profit_day，"当日结存"映射到daily_balance_day，"上日结存"映射到previous_balance_day'
                )
                keyword_mapping = {
                    **base_keyword_mapping,
                    "当日盈亏": "daily_profit_day",
                    "当日结存": "daily_balance_day",
                    "上日结存": "previous_balance_day",
                }
            else:  # 逐笔对冲
                logger.info(
                    '使用逐笔对冲模式，"平仓盈亏"映射到close_profit_trade，"当日结存"映射到daily_balance_trade，"浮动盈亏"映射到float_profit_trade，"上日结存"映射到previous_balance_trade'
                )
                keyword_mapping = {
                    **base_keyword_mapping,
                    "平仓盈亏": "close_profit_trade",
                    "当日结存": "daily_balance_trade",
                    "浮动盈亏": "float_profit_trade",
                    "上日结存": "previous_balance_trade",
                }

            logger.info(f"关键字映射表 - 包含{len(keyword_mapping)}个关键字")
            for chinese_key, english_key in keyword_mapping.items():
                logger.debug(f'  - "{chinese_key}" -> "{english_key}"')

            # 统计变量
            non_empty_rows = 0
            matched_cells = 0
            parsed_fields = {}
            found_keywords = {}

            # 逐行扫描
            for row_idx in range(total_rows):
                # 限制扫描行数，避免处理过大的数据
                if row_idx >= 100:
                    logger.info(f"已扫描100行，停止扫描以优化性能")
                    break

                # 获取原始行数据和字符串转换后的行数据
                row = df.iloc[row_idx]
                row_str = df_str[row_idx]

                # 过滤掉空行
                if row.isna().all():
                    logger.debug(f"行{row_idx+1}是空行，跳过")
                    continue

                non_empty_rows += 1

                # 记录非空行的基本信息
                non_na_count = row.notna().sum()
                logger.info(f"处理行 {row_idx+1} - 非空单元格数: {non_na_count}")

                # 记录每一行的内容（限制显示，避免日志过大）
                if row_idx < 5 or row_idx % 20 == 0:  # 只记录前5行和每20行
                    logger.info(
                        f"行{row_idx}内容: {[str(cell).strip() for cell in row_str[:5]]}..."
                    )

                # 显示行的前5个非空单元格
                visible_cells = []
                for idx, val in enumerate(row):
                    if pd.notna(val) and len(visible_cells) < 5:
                        visible_cells.append(f'[{idx+1}]"{str(val).strip()}"')
                logger.info(
                    f"行{row_idx+1}前{len(visible_cells)}个非空单元格: {', '.join(visible_cells)}"
                )

                # 检查行中是否包含关键字
                row_has_keyword = False
                for col_idx, cell in enumerate(row_str):
                    cell_str = str(cell).strip()
                    logger.debug(f'检查单元格 (行{row_idx}, 列{col_idx}): "{cell_str}"')

                    if not cell_str:  # 跳过空字符串
                        continue

                    # 检查是否包含任何关键字
                    for keyword, field in keyword_mapping.items():
                        # 只处理尚未解析的字段
                        if field in fund_data:
                            continue

                        if keyword in cell_str:
                            row_has_keyword = True
                            matched_cells += 1
                            found_keywords[keyword] = (row_idx, col_idx)
                            logger.info(
                                f'在行{row_idx+1}列{col_idx+1}发现关键字: "{keyword}" (单元格内容: "{cell_str}")'
                            )

                            # 直接从关键字所在单元格右边第二单元格获取值
                            value_extracted = False

                            # 直接尝试右侧第二单元格 (col_idx + 2)
                            if col_idx + 2 < len(row_str):
                                right_cell = str(row_str[col_idx + 2]).strip()
                                logger.info(
                                    f'关键字右侧第二单元格 (行{row_idx}, 列{col_idx+2}) 内容: "{right_cell}"'
                                )

                                try:
                                    # 处理可能包含逗号的数字字符串
                                    value_str = right_cell.replace(",", "")
                                    logger.info(f'处理后的值字符串: "{value_str}"')

                                    # 特殊处理风险度（可能包含%符号）
                                    if keyword == "风险度" and "%" in value_str:
                                        logger.info(
                                            f'发现风险度包含%符号: "{value_str}"'
                                        )
                                        value_str = value_str.replace("%", "")
                                        logger.info(f'风险度特殊处理后: "{value_str}"')
                                        # 转换为小数格式（除以100）
                                        try:
                                            value_float = float(value_str)
                                            if (
                                                value_float > 1
                                            ):  # 如果大于1，可能是百分比格式
                                                value_float = value_float / 100
                                                logger.info(
                                                    f"风险度转换为小数格式: {value_float}"
                                                )
                                            fund_data[field] = value_float
                                            logger.info(
                                                f"成功解析风险度为{fund_data[field]}"
                                            )
                                            value_extracted = True

                                            # 记录解析结果
                                            parsed_fields[keyword] = {
                                                "value": fund_data[field],
                                                "row": row_idx + 1,
                                                "column": col_idx + 1,
                                            }
                                            break
                                        except ValueError:
                                            logger.warning(
                                                f"风险度特殊处理后仍无法转换为数值"
                                            )
                                            # 继续尝试正常处理

                                    # 正常处理其他字段
                                    try:
                                        fund_data[field] = float(value_str)
                                        logger.info(
                                            f"成功解析{keyword}为{fund_data[field]}，对应字段{field}"
                                        )
                                        value_extracted = True

                                        # 记录解析结果
                                        parsed_fields[keyword] = {
                                            "value": fund_data[field],
                                            "row": row_idx + 1,
                                            "column": col_idx + 1,
                                        }
                                        break
                                    except (ValueError, TypeError) as e:
                                        logger.warning(
                                            f"无法从右侧单元格解析数值: {str(e)}，尝试从当前单元格提取"
                                        )
                                        # 如果右侧单元格不是数值，尝试当前单元格中的数值部分
                                        import re

                                        match = re.search(r"[\d.,]+%?", cell_str)
                                        if match:
                                            value_str = (
                                                match.group()
                                                .replace(",", "")
                                                .replace("%", "")
                                            )
                                            logger.info(
                                                f'从当前单元格提取的数值字符串: "{value_str}"'
                                            )
                                            try:
                                                fund_data[field] = float(value_str)
                                                logger.info(
                                                    f"从当前单元格成功解析{keyword}为{fund_data[field]}"
                                                )
                                                value_extracted = True

                                                # 记录解析结果
                                                parsed_fields[keyword] = {
                                                    "value": fund_data[field],
                                                    "row": row_idx + 1,
                                                    "column": col_idx + 1,
                                                }
                                            except (ValueError, TypeError) as e2:
                                                logger.warning(
                                                    f"无法解析{keyword}对应的值: {match.group()}，错误: {str(e2)}"
                                                )
                                except Exception as e:
                                    logger.error(
                                        f"处理关键字{keyword}时发生错误: {str(e)}"
                                    )
                            else:
                                logger.warning(
                                    f"行{row_idx+1}列{col_idx+1} - 找到{keyword}，但右侧第二单元格不存在"
                                )
                                continue
                            # 已在前面的else分支中处理了右侧第二单元格不存在的情况

                if row_has_keyword:
                    logger.info(f"行{row_idx+1} - 包含关键字，已处理")

            # 记录详细的处理统计
            logger.info("=" * 80)
            logger.info("扫描和解析统计:")
            logger.info(f"  扫描总行数: {row_idx+1}")
            logger.info(f"  非空行数: {non_empty_rows}")
            logger.info(f"  匹配的关键字数量: {matched_cells}")
            logger.info(f"  成功解析的字段数: {len(fund_data)}")
            logger.info(
                f"  未解析的关键字: {[k for k, v in keyword_mapping.items() if v not in fund_data]}"
            )

            # 输出找到的关键字位置
            logger.info(f"关键字搜索结果: 找到 {len(found_keywords)} 个关键字")
            for keyword, (row, col) in found_keywords.items():
                logger.info(f"  - {keyword}: 位置(行{row}, 列{col})")

            # 详细记录解析结果
            logger.info("解析结果详情:")
            for chinese_key, english_key in keyword_mapping.items():
                if english_key in fund_data:
                    info = parsed_fields.get(chinese_key, {})
                    logger.info(
                        f"  [已解析] {chinese_key} -> {english_key}: {fund_data[english_key]} "
                        + f"(行{info.get('row', '?')}, 列{info.get('column', '?')})"
                    )
                else:
                    logger.info(f"  [未解析] {chinese_key} -> {english_key}: 未找到")

            logger.info(f"解析结果完整字典: {fund_data}")

            # 检查必要字段是否存在
            required_fields = [
                "equity"
            ]  # 客户权益是必须的，上日结存根据模式不同使用不同字段
            logger.info("=" * 80)
            logger.info("必要字段验证:")
            logger.info(f"  必要字段列表: {required_fields}")

            missing_fields = [
                field for field in required_fields if field not in fund_data
            ]
            found_required_fields = [
                field for field in required_fields if field in fund_data
            ]

            logger.info(f"  找到的必要字段: {found_required_fields}")
            logger.info(f"  缺失的必要字段: {missing_fields}")

            if not missing_fields:
                logger.info("✅ 所有必要字段验证通过！")
                logger.info(f"已解析的完整字段列表: {list(fund_data.keys())}")

                # 对必要字段进行值验证
                for field in found_required_fields:
                    value = fund_data[field]
                    if isinstance(value, float) and np.isnan(value):
                        logger.warning(f"必要字段{field}的值为NaN，将进行处理")
                    else:
                        logger.info(f"必要字段{field}的值有效: {value}")

                # 处理NaN值，确保不会将NaN插入到数据库
                logger.info("开始处理可能的NaN值")
                cls._handle_nan_values(fund_data)
                logger.info("NaN值处理完成")
                logger.info("=" * 80)
                return fund_data
            else:
                logger.warning(f"❌ 未能找到必要字段: {missing_fields}")
                logger.warning(f"已找到的所有字段: {list(fund_data.keys())}")
                logger.warning(f"当前解析值: {fund_data}")
                logger.info("=" * 80)
        except Exception as e:
            logger.error(f"统一解析方法失败: {str(e)}", exc_info=True)
            logger.debug(f"异常详情: {repr(e)}")
            logger.info("=" * 80)

            # 即使出现异常，也检查必要字段是否存在
            required_fields = [
                "equity"
            ]  # 客户权益是必须的，上日结存根据模式不同使用不同字段
            if all(field in fund_data for field in required_fields):
                # 处理NaN值，确保不会将NaN插入到数据库
                cls._handle_nan_values(fund_data)
                return fund_data
            else:
                missing_fields = [
                    field for field in required_fields if field not in fund_data
                ]
                logger.warning(f"解析失败，未能找到必要字段: {missing_fields}")

        # 方法3：尝试计算缺失字段（根据报表逻辑关系）
        try:
            logger.info("=" * 80)
            logger.info("开始方法3：通过报表逻辑计算缺失字段 - 增强日志模式")

            # 记录当前状态
            logger.info("方法3: 计算前状态统计")
            logger.info(f"  当前已解析字段数: {len(fund_data)}")
            logger.info(f"  当前字段列表: {list(fund_data.keys())}")
            logger.info(f'  客户权益存在性: {"是" if "equity" in fund_data else "否"}')
            logger.info(
                f'  上日结存(逐日盯市)存在性: {"是" if "previous_balance_day" in fund_data else "否"}'
            )
            logger.info(
                f'  上日结存(逐笔对冲)存在性: {"是" if "previous_balance_trade" in fund_data else "否"}'
            )

            # 计算计数器
            calculation_stats = {
                "attempted_calculations": 0,
                "successful_calculations": 0,
                "failed_calculations": 0,
            }

            # 如果客户权益缺失，但有其他相关字段，尝试计算
            if "equity" not in fund_data:
                logger.info("方法3: 客户权益(equity)缺失，开始尝试计算")
                calculation_stats["attempted_calculations"] += 1

                # 客户权益计算方法1：当日结存 + 浮动盈亏
                if (
                    "daily_balance_day" in fund_data
                    and "float_profit_trade" in fund_data
                ):
                    logger.info(
                        "方法3: 计算方法1 - equity = daily_balance_day + float_profit_trade"
                    )
                    logger.info(
                        f'  daily_balance_day = {fund_data["daily_balance_day"]}'
                    )
                    logger.info(
                        f'  float_profit_trade = {fund_data["float_profit_trade"]}'
                    )

                    # 验证计算值
                    daily_balance = fund_data["daily_balance_day"]
                    float_profit = fund_data["float_profit_trade"]

                    if isinstance(daily_balance, (int, float)) and isinstance(
                        float_profit, (int, float)
                    ):
                        equity_value = daily_balance + float_profit
                        fund_data["equity"] = equity_value
                        logger.info(f"方法3: 计算成功! 客户权益 = {equity_value}")
                        calculation_stats["successful_calculations"] += 1
                    else:
                        logger.warning(f"方法3: 计算失败 - 字段类型错误")
                        logger.warning(
                            f"  daily_balance_day类型: {type(daily_balance).__name__}"
                        )
                        logger.warning(
                            f"  float_profit_trade类型: {type(float_profit).__name__}"
                        )
                        calculation_stats["failed_calculations"] += 1

                # 客户权益计算方法2：保证金占用 + 可用资金
                elif (
                    "margin_used" in fund_data
                    and "available_funds" in fund_data
                    and "equity" not in fund_data
                ):
                    logger.info(
                        "方法3: 计算方法2 - equity = margin_used + available_funds"
                    )
                    logger.info(f'  margin_used = {fund_data["margin_used"]}')
                    logger.info(f'  available_funds = {fund_data["available_funds"]}')

                    # 验证计算值
                    margin = fund_data["margin_used"]
                    available = fund_data["available_funds"]

                    if isinstance(margin, (int, float)) and isinstance(
                        available, (int, float)
                    ):
                        equity_value = margin + available
                        fund_data["equity"] = equity_value
                        logger.info(f"方法3: 计算成功! 客户权益 = {equity_value}")
                        calculation_stats["successful_calculations"] += 1
                    else:
                        logger.warning(f"方法3: 计算失败 - 字段类型错误")
                        logger.warning(f"  margin_used类型: {type(margin).__name__}")
                        logger.warning(
                            f"  available_funds类型: {type(available).__name__}"
                        )
                        calculation_stats["failed_calculations"] += 1

                # 客户权益计算方法3：上日结存 + 当日存取 + 当日总盈亏
                elif (
                    (
                        "previous_balance_day" in fund_data
                        or "previous_balance_trade" in fund_data
                    )
                    and "daily_deposit_withdrawal" in fund_data
                    and "daily_profit_day" in fund_data
                    and "equity" not in fund_data
                ):
                    # 优先使用逐日盯市的上日结存
                    prev_balance_key = (
                        "previous_balance_day"
                        if "previous_balance_day" in fund_data
                        else "previous_balance_trade"
                    )
                    logger.info(
                        f"方法3: 计算方法3 - equity = {prev_balance_key} + daily_deposit_withdrawal + daily_profit_day"
                    )
                    logger.info(f"  {prev_balance_key} = {fund_data[prev_balance_key]}")
                    logger.info(
                        f'  daily_deposit_withdrawal = {fund_data["daily_deposit_withdrawal"]}'
                    )
                    logger.info(f'  daily_profit_day = {fund_data["daily_profit_day"]}')

                    # 验证计算值
                    prev_balance = fund_data[prev_balance_key]
                    deposit = fund_data["daily_deposit_withdrawal"]
                    profit = fund_data["daily_profit_day"]

                    if all(
                        isinstance(x, (int, float))
                        for x in [prev_balance, deposit, profit]
                    ):
                        equity_value = prev_balance + deposit + profit
                        fund_data["equity"] = equity_value
                        logger.info(f"方法3: 计算成功! 客户权益 = {equity_value}")
                        calculation_stats["successful_calculations"] += 1
                    else:
                        logger.warning(f"方法3: 计算失败 - 字段类型错误")
                        calculation_stats["failed_calculations"] += 1
                else:
                    logger.warning("方法3: 没有足够的字段进行客户权益计算")
                    logger.warning("方法3: 可用的计算字段不足，无法执行计算")

            # 记录计算统计
            logger.info("=" * 80)
            logger.info("方法3: 计算统计结果:")
            logger.info(
                f"  尝试计算次数: {calculation_stats['attempted_calculations']}"
            )
            logger.info(
                f"  成功计算次数: {calculation_stats['successful_calculations']}"
            )
            logger.info(f"  失败计算次数: {calculation_stats['failed_calculations']}")

            # 记录计算后的状态
            logger.info("方法3: 计算后状态:")
            logger.info(f"  字段数: {len(fund_data)}")
            logger.info(f"  字段列表: {list(fund_data.keys())}")
            logger.info(f"  客户权益: {fund_data.get('equity', '缺失')}")

            # 检查必要字段是否存在
            if "equity" in fund_data:
                equity_value = fund_data["equity"]
                logger.info("方法3: ✅ 计算成功，客户权益已通过计算获得")
                logger.info(f"  客户权益值: {equity_value}")
                logger.info(f"  客户权益类型: {type(equity_value).__name__}")

                # 验证计算结果的合理性
                if isinstance(equity_value, (int, float)):
                    if equity_value < 0:
                        logger.warning(
                            "方法3: ⚠️  计算得到的客户权益为负值，这可能表明数据异常"
                        )
                    elif equity_value == 0:
                        logger.warning(
                            "方法3: ⚠️  计算得到的客户权益为零，这可能表明数据缺失"
                        )
                    else:
                        logger.info("方法3: 客户权益值看起来合理")

                # 处理NaN值，确保不会将NaN插入到数据库
                logger.info("方法3: 开始处理可能的NaN值")
                cls._handle_nan_values(fund_data)
                logger.info("方法3: NaN值处理完成")
                logger.info("=" * 80)
                return fund_data
            else:
                logger.warning("方法3: ❌ 未能计算出客户权益")
            logger.warning("方法3: 所有计算方法都尝试失败")
            logger.info("=" * 80)
        except Exception as e:
            logger.error(f"方法3计算缺失字段失败: {str(e)}", exc_info=True)
            logger.debug(f"方法3异常详情: {repr(e)}")
            logger.info("=" * 80)

        # 如果以上方法都失败，尝试基本的默认值设置
        logger.error("=" * 80)
        logger.error("所有解析方法都失败！无法从结算单中解析出有效的资金信息")
        logger.error(f"最终解析结果: {fund_data}")
        logger.error("请检查Excel文件格式是否正确，确保包含必要的关键字段")
        logger.error("=" * 80)

        default_data = {
            "previous_balance_day": 0.0,
            "previous_balance_trade": 0.0,
            "daily_deposit_withdrawal": 0.0,
            "daily_profit_day": 0.0,
            "daily_balance_day": 0.0,
            "close_profit_trade": 0.0,
            "daily_balance_trade": 0.0,
            "float_profit_trade": 0.0,
            "daily_fee": 0.0,
            "equity": 0.0,
            "margin_used": 0.0,
            "available_funds": 0.0,
            "risk_ratio": 0.0,
        }
        logger.info("使用默认值返回")
        return default_data

    @classmethod
    def _handle_nan_values(cls, fund_data: dict) -> None:
        """
        处理资金数据字典中的无效值
        对于无效值（None、NaN等），从字典中删除而不是设置为0.0
        这是为了避免覆盖数据库中已有的值

        :param fund_data: 解析后的资金数据字典
        """
        import numpy as np

        # 处理无效值
        for key, value in list(fund_data.items()):
            # 检查是否为None
            if value is None:
                del fund_data[key]
            # 检查是否为NaN或无穷大
            elif isinstance(value, float) and (
                np.isnan(value) or np.isinf(value) or np.isneginf(value)
            ):
                del fund_data[key]
            # 尝试转换非数值类型
            elif not isinstance(value, (int, float)):
                try:
                    # 尝试转换为浮点数
                    if isinstance(value, str):
                        # 清理字符串（去除空白、逗号等）
                        cleaned_value = value.strip().replace(",", "")
                        fund_data[key] = float(cleaned_value)
                    else:
                        fund_data[key] = float(value)
                except (ValueError, TypeError):
                    # 如果转换失败，删除该字段
                    del fund_data[key]
